﻿using log4net;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using UpLoadDataOnTime.DataBaseUtil;
using static UpLoadDataOnTime.DataBaseUtil.SqlCollection;

namespace UpLoadDataOnTime
{
    public class SqlHelper
    {
        ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        private static SqlHelper sqlHelper;

        // 定义一个标识确定线程同步，排除多线程下实例化多个数据库服务对象而违背单例模式
        private static readonly object locker = new object();

        public static SqlHelper open()
        {
            if (sqlHelper == null)
            {
                lock (locker)
                {
                    if (sqlHelper == null)
                    {
                        sqlHelper = new SqlHelper();
                    }
                }
            }
            return sqlHelper;
        }

        /// <summary>
        /// 指定查询返回多条数据
        /// </summary>
        /// <param name="sql">sql text</param>
        /// <param name="commandtype">sql类型,默认执行查询sql</param>
        /// <param name="parameters">sql param</param>
        /// <returns></returns>
        public List<T> QueryMany<T>(string connStr,string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class, new()
        {
            // ADO操作数据库第一步：实例化SqlConnection对象，打开一个数据库连接
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                // 数据库操作类
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    // 设置执行类型，sql or proc 
                    comm.CommandType = commandtype;
                    // 设置参数
                    if (parameters != null)
                    {
                        foreach (SqlParameter item in parameters)
                        {
                            comm.Parameters.Add(item);
                        }
                    }
                    // 声明数据库适配器，通过comm来实例化
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(comm);
                    sqlDataAdapter.SelectCommand.CommandTimeout = 0;
                    // 声明一个DataSet用于装载查询结果且将sqlDataAdapter的数据填充(Fill())到DataSet中
                    DataTable dt = new DataTable();
                    try
                    {
                        sqlDataAdapter.Fill(dt);
                    }
                    catch (Exception e)
                    {
                        log.Error(e.ToString());   
                    }
                    comm.Parameters.Clear();
                    // 序列化数据
                    List<T> dataList = SerializeData.ToEntities<T>(dt);
                    return dataList;
                }
            }
        }

        /// <summary>
        /// 指定查询返回首行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">sql text</param>
        /// <param name="parameters">sql类型,默认执行查询sql</param>
        /// <param name="commandtype">sql param</param>
        /// <returns></returns>
        public T QuerySingle<T>(string connStr, string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class, new()
        {
            var resultList = QueryMany<T>(connStr,sql, parameters);
            return resultList.FirstOrDefault<T>();
        }

        /// <summary>
        /// 数据插入操作
        /// </summary>
        /// <param name="sql">sql text</param>
        /// <param name="parameters">sql param</param>
        /// <returns></returns>
        public int Insert(string connStr, string sql, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(connStr,sql, parameters);
        }

        public int Insert<T>(string sql, SqlParameter[] parameters) where T : class, new() { return 0; }

        /// <summary>
        /// 数据库更新操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Update(string connStr, string sql, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(connStr,sql, parameters);
        }

        public int Update<T>(string sql, SqlParameter[] parameters) where T : class, new() { return 0; }

        /// <summary>
        /// 数据库增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="commandtype"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string connStr, string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    comm.CommandType = commandtype;
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            comm.Parameters.Add(parameter);
                        }
                    }
                    conn.Open();
                    int count = comm.ExecuteNonQuery(); // 返回受影响函数
                    comm.Parameters.Clear();
                    conn.Close();
                    return count;
                }
            }
        }

        public int ExecuteNonQuery<T>(string sql, SqlParameter[] parameters, CommandType commandtype = CommandType.Text) where T : class, new()
        {
            return 0;
        }

        /// <summary>
        /// 数据库执行事务操作
        /// </summary>
        /// <param name="list">sql集合</param>
        /// <returns></returns>
        public OptionTranResult ExecuteTran(string connStr, List<SqlCollection> list)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                // 打开数据库链接
                conn.Open();
                // 开启事务
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    // 实例化执行对象
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.Transaction = tran;
                        int count = 0;
                        int row = 0;
                        foreach (var item in list)
                        {
                            switch (item.sqlType)
                            {
                                case SqlType.Insert:
                                case SqlType.Delete:
                                case SqlType.Update:
                                    comm.CommandType = CommandType.Text;
                                    // sql text
                                    comm.CommandText = item.sqlText;
                                    break;
                                case SqlType.Procedure:
                                    comm.CommandType = CommandType.StoredProcedure;
                                    // sql proc name
                                    comm.CommandText = item.sqlText;
                                    break;
                                default:
                                    comm.Parameters.Clear();
                                    conn.Close();
                                    return new OptionTranResult(false);
                            }
                            // 设置参数
                            if (item.parameters != null)
                            {
                                foreach (SqlParameter parameter in item.parameters)
                                {
                                    comm.Parameters.Add(parameter);
                                }
                            }

                            row = comm.ExecuteNonQuery(); // 返回受影响行数

                            comm.Parameters.Clear();

                            if (row <= 0)
                            {

                                comm.Parameters.Clear();

                                tran.Rollback();

                                conn.Close();

                                return new OptionTranResult(false);

                            }

                            // 累加受影响行数
                            count += row;
                        }

                        tran.Commit();

                        return new OptionTranResult(true, count);
                    }

                }

            }
        }

        /// <summary>
        /// 执行分页存储过程
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<T> ExexuteProc<T>(string connStr, string procName, ref SqlParameter[] parameters) where T : class, new()
        {

            return QueryMany<T>(connStr, procName, parameters, CommandType.StoredProcedure);
        }
    }

    public struct OptionTranResult
    {
        public bool Status;

        public int Count;

        public OptionTranResult(bool status, int count = 0)
        {
            this.Status = status;

            this.Count = count;
        }
    }
}
